﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Diagnostics;

using SubSonic;

using Northwind.Data;

namespace Subsonic2SqlserverDemo
{
    static class ProductsTester
    {
        // ================================================================== //
        #region [ helper methods ]

        private static void Print(this Product prod)
        {
            Console.WriteLine("id<{0}>-s<{1}>-c<{2}>-\"{3}\"",
                prod.ProductID,
                prod.SupplierID,
                prod.CategoryID,
                prod.ProductName);
        }

        private static void Print(this IEnumerable<Product> products)
        {
            Console.WriteLine("###### TOTAL NUMBER = [{0}] ######", products.Count());
            int index = 0;
            foreach (Product prod in products)
            {
                ++index;
                Console.Write("[{0}]: ", index);
                prod.Print();
            }
        }

        private static Product[] CreateProducts(int count)
        {
            Product[] products = new Product[count];
            for (int index = 0; index < products.Length; ++index)
            {
                products[index] = new Product
                {
                    ProductName = string.Format("cheka-batch-{0}", index),
                    Discontinued = (index % 2 == 0),
                    QuantityPerUnit = string.Format("test-batch-{0}", index)
                };
            }
            return products;
        }

        #endregion

        // ================================================================== //
        #region [ test ]

        // ***************************************** //
        #region < select >

        private static void TestSelectSingle()
        {
            Product prod = new Product(1);
            prod.Print();
        }

        private static void TestFetchById()
        {
            // ------------- fetch by a existing id
            Product prod = Product.FetchByID(4);
            Debug.Assert(prod.IsLoaded);
            prod.Print();

            // ------------- fetch by a non-existing id
            prod = Product.FetchByID(1000);
            Debug.Assert(prod == null);
            Console.WriteLine("FetchById return null for non-existing id");
        }

        /// <summary>
        /// unlike "SingleOrDefault" in Subsonic3.0
        /// given a non-existing id, the constructor will still create an instance for us
        /// but fill our property with default value
        /// and "IsLoaded" property returns false
        /// </summary>
        private static void TestSelectNonExisted()
        {
            // ---------------------------------- using constructor
            int nonId = 1000;
            Product nonExisted = new Product(nonId);

            Debug.Assert(0 == nonExisted.ProductID);
            Debug.Assert(string.IsNullOrEmpty(nonExisted.ProductName));
            Debug.Assert(false == nonExisted.IsLoaded);

            // ---------------------------------- using select
            nonExisted = Product.FetchByID(nonId);
            Debug.Assert(nonExisted == null);
            Console.WriteLine("select return null for non-existed id [{0}]", nonId.ToString());
        }

        private static void TestFetchByParameter()
        {
            using (IDataReader reader = Product.FetchByParameter(Product.Columns.SupplierID, 8))
            {
                int index = 0;
                while (reader.Read())
                {
                    ++index;
                    Console.WriteLine("[{0}] id={1} name={2}",
                        index.ToString(),
                        reader[Product.Columns.ProductID].ToString(),
                        reader[Product.Columns.ProductName].ToString());
                }
            }// using
        }

        /// <summary>
        /// this testcase shows that "Where" can be concatenated together
        /// </summary>
        private static void TestSelectMany()
        {
            ProductCollection prodCollection = new ProductCollection()
                .Where(Product.Columns.CategoryID, 2)
                .Where(Product.Columns.SupplierID, 3)
                .Load();
            prodCollection.Print();
        }

        private static void TestSelectMany2()
        {
            // directly create a new "select" by calling "new select()" is still OK
            ProductCollection prodCollection = DB.Select()
                .From<Product>()
                .Where(Product.Columns.CategoryID).IsEqualTo(2)
                .And(Product.Columns.SupplierID).IsEqualTo(3)
                .ExecuteAsCollection<ProductCollection>();
            prodCollection.Print();
        }

        private static void TestSelectManyReturnTypedList()
        {
            IList<Product> prodList = new Select()
                .From(Product.Schema)
                .Where(Product.Columns.CategoryID).IsEqualTo(3)
                .And(Product.Columns.SupplierID).IsEqualTo(8)
                .ExecuteTypedList<Product>();

            prodList.Print();
        }

        private static void TestFind()
        {
            Product requiredProd = new Product
            {
                SupplierID = 8,
                CategoryID = 3
            };

            int counter = 0;
            using (IDataReader reader = Product.Find(requiredProd))
            {
                while (reader.Read())
                {
                    ++counter;

                    int supplierId = (int)reader[Product.Columns.SupplierID];
                    int categoryId = (int)reader[Product.Columns.CategoryID];

                    Debug.Assert((supplierId == requiredProd.SupplierID)
                        && (categoryId == requiredProd.CategoryID));
                }
            }
            Console.WriteLine("{0} items qualified", counter.ToString());
        }

        private static void TestFillCollectionByReader()
        {
            Product requiredProd = new Product
            {
                SupplierID = 8,
                CategoryID = 3
            };

            ProductCollection prodCollection = new ProductCollection();
            prodCollection.LoadAndCloseReader(Product.Find(requiredProd));

            prodCollection.Print();
        }

        private static void TestIn()
        {
            ProductCollection prodCollection = DB.Select().From<Product>().Where(Product.Columns.CategoryID).In(1, 2).ExecuteAsCollection<ProductCollection>();
            prodCollection.Print();
        }

        private static void TestIn2()
        {
            ProductCollection prodCollection = DB.Select()
                .From(Product.Schema)
                .Where(Product.Columns.ProductID).In(19, 20)
                .ExecuteAsCollection<ProductCollection>();
            prodCollection.Print();
        }

        #endregion

        // ***************************************** //
        #region < insert >

        /// <summary>
        /// this test shows that after call "Save", subsonic baseclass will do the synchronization for us
        /// such as loading auto-generated keys or modify the status
        /// </summary>
        private static void TestSingleInsert()
        {
            Product newProd = new Product
            {
                ProductName = "cheka test",
                Discontinued = true
            };

            Debug.Assert(newProd.IsNew & newProd.ProductID == 0);
            newProd.Save();
            Debug.Assert(newProd.IsLoaded & newProd.ProductID > 0);

            Console.WriteLine("id for newly added product = {0}", newProd.ProductID);
        }

        private static void TestBatchInsert()
        {
            Product[] products = CreateProducts(4);

            // check before inserting
            Debug.Assert(products.All(prod => prod.IsNew & prod.ProductID == 0));

            ProductCollection prodCollection = new ProductCollection();
            prodCollection.AddRange(products);
            prodCollection.BatchSave();

            // check after inserting
            // chekanote: this test just shows that : different from "SingleSave" that when after saving, Subsonic base class
            // will modify the status of the object, and retrieve the auto-generated id from the database
            // when you use "BatchSave", you should pay attention that what we are using is "BatchSave"
            // not "BatchInsert", subsonic base class will not do the Object-Relation synchronization for us
            // the object collection will still remain "New", "Modified" or "Deleted", you should synchronize yourself
            // such as create another new collection and reload
            Debug.Assert(products.All(prod => (prod.IsNew) & (prod.ProductID == 0)));
            Debug.Assert(prodCollection.All(prod => (prod.IsNew) & (prod.ProductID == 0)));

            Console.WriteLine("Done !!");
        }

        #endregion

        // ***************************************** //
        #region < update >

        private static void TestSingleUpdate()
        {
            int id = 128;

            // ------------------- load
            Product chaningProd = new Product(id);
            Debug.Assert(chaningProd.IsLoaded);
            Debug.Assert(!chaningProd.IsDirty);

            // ------------------- modify
            chaningProd.ProductName = "cheka modified";
            chaningProd.Discontinued = !chaningProd.Discontinued;
            Debug.Assert(chaningProd.IsDirty);

            // ------------------- save
            chaningProd.Save();// base class will synchronize the status for us
            Debug.Assert(chaningProd.IsLoaded);
            Debug.Assert(!chaningProd.IsDirty);

            // ------------------- reload
            Product newProduct = new Product(id);
            Debug.Assert(newProduct.ProductName.Equals(chaningProd.ProductName));
            Debug.Assert(newProduct.Discontinued == chaningProd.Discontinued);

            Console.WriteLine("update successfully !!");
        }

        private static void TestMultiUpdate()
        {
            SqlQuery cmd = new Update(Product.Schema)
                .Set(Product.Columns.Discontinued).EqualTo(false)
                .Where(Product.Columns.SupplierID).IsEqualTo(8)
                .And(Product.Columns.CategoryID).IsEqualTo(3);

            int affectedNumber = cmd.Execute();

            Console.WriteLine("[SQL]");
            Console.WriteLine(cmd.SQLCommand);

            Console.WriteLine("[BuildSqlStatement]");
            Console.WriteLine(cmd.BuildSqlStatement());

            Console.WriteLine("{0} rows affected", affectedNumber);
        }

        #endregion

        // ***************************************** //
        #region < delete >

        private static void TestSingleDelete()
        {
            int id = 125;
            Product.Delete(id);

            Product deletedProd = Product.FetchByID(id);
            Debug.Assert(deletedProd == null);
            Console.WriteLine("product with id={0} has been deleted.", id.ToString());
        }

        private static void TestBatchDelete()
        {
            SqlQuery cmd = DB.Delete().From(Product.Schema).Where(Product.Columns.ProductName).Like("%cheka%");
            Console.WriteLine("[sql statement]: \n{0}", cmd.BuildSqlStatement());

            int affectedNumber = cmd.Execute();
            Console.WriteLine("{0} rows have been deleted.", affectedNumber.ToString());
        }

        #endregion

        // ***************************************** //
        #region [ aggregate ]

        private static void TestSelectMaxId()
        {
            int maxId = DB.Select(Aggregate.Max(Product.Columns.ProductID)).From(Product.Schema).ExecuteScalar<int>();
            Console.WriteLine("max id = {0}", maxId.ToString());
        }

        #endregion

        // ***************************************** //

        private static void TestRecordCount()
        {
            int total = new Select(Product.Columns.ProductID).From(Tables.Product).GetRecordCount();
            Console.WriteLine("table<{0}> has {1} items", Tables.Product, total.ToString());
        }

        private static void TestInnerJoin()
        {
            OrderDetailCollection orderDetails = DB.Select()
                .From(Tables.OrderDetail)
                .InnerJoin(Tables.Product)
                .Where(Product.Columns.CategoryID).IsEqualTo(3)
                .And(OrderDetail.Columns.ProductID).IsEqualTo(16)
                .ExecuteAsCollection<OrderDetailCollection>();

            orderDetails.Print();
        }

        private static void TestLike()
        {
            ProductCollection prodCollection = DB.Select()
                .From<Product>()
                .Where(Product.Columns.ProductName)
                .Like("%cheka%").ExecuteAsCollection<ProductCollection>();
            prodCollection.Print();
        }

        private static void TestReferenceAsChild()
        {
            Product prod = new Product(5);

            Console.WriteLine("category of product<{0},{1}> is: ", prod.ProductID, prod.ProductName);
            prod.Category.Print();

            Console.WriteLine("supplier of product<{0},{1}> is: ", prod.ProductID, prod.ProductName);
            prod.Supplier.Print();
        }

        private static void TestReferenceAsParent()
        {
            Product prod = new Product(5);
            OrderDetailCollection ordCollection = prod.OrderDetails();

            Console.WriteLine("order details related to product<{0},{1}>", prod.ProductID, prod.ProductName);
            ordCollection.Print();
        }

        public static void TestParentBatchSave()
        {
            Product prod = new Product(5);
            OrderDetailCollection changeOrdCollection = prod.OrderDetails();
            int oriCount = changeOrdCollection.Count;

            // ------------------------------- update
            foreach (OrderDetail ord in changeOrdCollection)
            {
                ord.UnitPrice += 10;
                Debug.Assert(ord.IsDirty);
            }

            // ------------------------------- add new one
            OrderDetail newOrd = new OrderDetail
            {
                ProductID = prod.ProductID,
                OrderID = 10250, // must be a valid foreign key
                UnitPrice = 10,
                Quantity = 10,
                Discount = 0.5f
            };
            changeOrdCollection.Add(newOrd);
            // by Reflector, we can see that "SaveAll" is just loop and call each element's Save one by one (may use different connections)
            // while BatchSave will execute multiple commands within a single transcation, and with a single, same connection 
            changeOrdCollection.BatchSave();

            // ------------------------------- reselect
            OrderDetailCollection newOrdCollection = prod.OrderDetails();
            Debug.Assert(newOrdCollection.Count == oriCount + 1);
            Console.WriteLine("DONE !!");
        }

        private static void TestToXML()
        {
            Product prod = Product.FetchByID(4);
            Console.WriteLine(prod.ToXML());
        }

        #endregion

        // ================================================================== //
        #region [ main entry ]

        public static void TestMain()
        {
            //TestFetchById();
            //TestSelectMany();
            //TestSelectMany2();
            //TestSingleInsert();
            //TestBatchInsert();
            //TestSingleUpdate();
            //TestSelectNonExisted();
            //TestSingleDelete();
            //TestRecordCount();
            //TestInnerJoin();
            //TestLike();
            //TestIn();
            //TestIn2();
            //TestReferenceAsChild();
            //TestReferenceAsParent();
            //TestParentBatchSave();
            //TestToXML();
            //TestFetchByParameter();
            //TestFind();
            //TestFillCollectionByReader();
            TestMultiUpdate();
            //TestSelectManyReturnTypedList();
            //TestBatchDelete();
            //TestSelectMaxId();
        }

        #endregion
    }
}